电影 - CS50x 2023

编写 SQL 查询来回答有关电影数据库的问题。

入门

接下来执行

wget https://cdn.cs50.net/2022/fall/psets/7/movies.zip

以便将名为 movies.zip 的 ZIP 文件下载到你的代码空间。

然后执行

来创建一个名为 movies 的文件夹。你不再需要此ZIP文件，可以执行

并在提示符后输入“y”，然后按回车键删除下载的ZIP文件。

现在输入

然后按回车键进入该目录。现在你的提示符应该如下所示。

单独执行 ls，你应该会看到 13 个 .sql 文件，以及 movies.db。

如果遇到问题，请重复以上步骤，检查哪里出错！

理解

提供给你的 movies.db 文件是一个 SQLite 数据库，存储了来自 IMDb 的电影、导演、演员及其评分数据。在终端窗口运行 sqlite3 movies.db，即可开始查询数据库。

当 sqlite3 提示输入查询时，键入 .schema 并按回车键。这会输出用于创建数据库表的 CREATE TABLE 语句。通过检查这些语句，可以了解每个表的列。

注意，movies 表包含一个 id 列，用于唯一标识每部电影，以及 title (电影名) 和 year (发行年份) 列。 people 表也有一个 id 列，并且还有每个人的 name 和 birth 年份的列。

电影评分存储在 ratings 表。该行的其余部分包含每部电影的 rating (评分) 和在 IMDb 上获得的 votes (投票数) 数据。

stars 和 directors 表将演员和导演与其参演或执导的电影关联起来。（仅包括主要明星和导演。）每个表只有两列：movie_id 和 person_id，它们分别引用特定的电影和人员。

你的任务是编写 SQL 查询，从这些表中选择数据来回答各种问题。

规范

针对以下每个问题，你需要编写一个 SQL 查询来输出指定结果。你的答案必须是单个 SQL 查询语句，但允许嵌套其他查询。你不应对任何电影或人物的 id 做任何假设：即使电影或人物的 id 发生变化，你的查询也应保持准确。最后，每个查询应仅返回回答问题所需的数据：例如，如果问题只要求输出电影名称，则查询不应包含电影的发行年份。

欢迎对照 IMDb 检查查询结果。请注意，网站上的评分可能与 movies.db 中的评分有所不同，因为自数据下载后可能产生了更多投票。

在 1.sql 中，编写 SQL 查询，列出所有在 2008 年上映的电影的标题。
- 查询结果应为一个表格，包含一列，列出每部电影的标题。
在 2.sql 中，编写 SQL 查询，确定艾玛·斯通 (Emma Stone) 的出生年份。
- 查询应返回一个表格，包含一列和一行（不包括标题），其中包含艾玛·斯通的出生年份。
- 你可以假设数据库中只有一个名叫艾玛·斯通的人。
在 3.sql 中，编写 SQL 查询，按字母顺序排列，列出所有上映日期在 2018 年或之后的电影的标题。
- 查询结果应为一个表格，包含一列，列出每部电影的标题。
- 2018 年上映的电影应该包括在内，未来上映的电影也应该包括在内。
在 4.sql 中，编写 SQL 查询，确定 IMDb 评分为 10.0 的电影数量。
- 查询应返回一个表格，包含一列和一行（不包括标题），其中包含评分为 10.0 的电影数量。
在 5.sql 中，编写 SQL 查询，按时间顺序排列，列出所有哈利·波特 (Harry Potter) 系列电影的标题和上映年份。
- 查询应返回一个表格，包含两列，一列列出每部电影的标题，另一列列出每部电影的上映年份。
- 你可以假设所有哈利·波特系列电影的标题都以“Harry Potter”开头。
在 6.sql 中，编写 SQL 查询，确定所有在 2012 年上映的电影的平均评分。
- 查询应返回一个表格，包含一列和一行（不包括标题），其中包含平均评分。
在 7.sql 中，编写 SQL 查询，列出所有在 2010 年上映的电影及其评分，按评分降序排列。对于评分相同的电影，按标题字母顺序排列。
- 查询应返回一个表格，包含两列，一列列出每部电影的标题，另一列列出每部电影的评分。
- 不包含评分的电影不应包含在结果中。
在 8.sql 中，编写 SQL 查询，列出所有参演《玩具总动员》(Toy Story) 的姓名。
- 查询应返回一个表格，包含一列，列出每个人的姓名。
- 你可以假设数据库中只有一部名为《玩具总动员》的电影。
在 9.sql 中，编写 SQL 查询，列出所有出演 2004 年上映的电影的演员姓名，按出生年份排序。
- 查询应返回一个表格，包含一列，列出每个人的姓名。
- 出生年份相同的人可以按任何顺序排列。
- 只需列出有出生年份记录的演员即可。
- 如果一个人在 2004 年出演了多部电影，他们应该只在你的结果中出现一次。
在 10.sql 中，编写 SQL 查询，列出所有执导过评分 9.0 及以上电影的导演姓名。
- 查询应返回一个表格，包含一列，列出每个人的姓名。
- 如果一个人执导了多部评分至少为 9.0 的电影，他们应该只在你的结果中出现一次。
在 11.sql 中，编写 SQL 查询，列出查德维克·博斯曼 (Chadwick Boseman) 主演的评分最高的五部电影标题（按评分降序排列）。
- 查询应返回一个表格，包含一列，列出每部电影的标题。
- 你可以假设数据库中只有一个名叫查德维克·博斯曼的人。
在 12.sql 中，编写 SQL 查询，列出布莱德利·库珀和詹妮弗·劳伦斯共同主演的所有电影的标题。
- 查询应返回一个表格，包含一列，列出每部电影的标题。
- 你可以假设数据库中只有一个名叫布莱德利·库珀的人。
- 你可以假设数据库中只有一个名叫詹妮弗·劳伦斯的人。
在 13.sql 中，编写 SQL 查询，列出所有与凯文·贝肯 (Kevin Bacon) 共同出演电影的演员姓名。
- 查询应返回一个表格，包含一列，列出每个人的姓名。
- 数据库中可能存在同名者，请务必选择 1958 年出生的凯文·贝肯。
- 凯文·贝肯本人不应包含在结果列表中。

演练

用法

要在 VS Code 中测试查询，您可以运行以下命令来查询数据库：

$ cat filename.sql | sqlite3 movies.db

其中 filename.sql 是包含 SQL 查询的文件。

您也可以运行

$ cat filename.sql | sqlite3 movies.db > output.txt

将查询的输出重定向到一个名为 output.txt 的文本文件。（这有助于检查查询返回的行数！）

提示

请参考 SQL 关键字，了解可能有用的 SQL 语法！
请参阅 sqlstyle.guide，获取关于SQL风格的建议，尤其是在查询变得复杂时！

测试

虽然可以使用 check50 检查此问题，但我们鼓励您自行测试以下各项。您可以运行 sqlite3 movies.db 在数据库上执行其他查询，以验证结果是否正确。

如果使用此问题集提供的 movies.db 数据库，您会发现

运行 1.sql 将得到一个包含 1 列和 10,050 行的表。
运行 2.sql 将得到一个包含 1 列和 1 行的表。
运行 3.sql 将得到一个包含 1 列和 88,918 行的表。
运行 4.sql 将得到一个包含 1 列和 1 行的表。
运行 5.sql 将得到一个包含 2 列和 12 行的表。
运行 6.sql 将得到一个包含 1 列和 1 行的表。
运行 7.sql 将得到一个包含 2 列和 7,085 行的表。
运行 8.sql 将得到一个包含 1 列和 4 行的表。
运行 9.sql 将得到一个包含 1 列和 18,946 行的表。
运行 10.sql 将得到一个包含 1 列和 3,392 行的表。
运行 11.sql 将得到一个包含 1 列和 5 行的表。
运行 12.sql 将得到一个包含 1 列和 4 行的表。
运行 13.sql 将得到一个包含 1 列和 182 行的表。

注意：行数不包含显示列名的标题行。

如果查询返回的行数与预期略有不同，请检查是否正确处理了重复数据！对于需要列出名字的查询，同一个人不应重复出现，但同名的不同人则应分别列出。

运行以下命令，使用 check50 评估代码的正确性。

check50 cs50/problems/2023/x/movies

如何提交

在终端中运行以下命令，提交您的作业。

submit50 cs50/problems/2023/x/movies

致谢

信息来源：IMDb (imdb.com)，已获授权使用。